from flask import jsonify,request,Blueprint,redirect
from db import db
from utils.myredis import r
import json
import datetime
goods_blue = Blueprint("goodsblue","__name__")


def getTypelist():
    # 获取缓存中数据
    categorie = r.str_get("categorie")
    # r.del_key(categorie)
    # 判断是否存在
    if categorie:
        categorie = json.loads(categorie)
    else:
        sql = "select * from categorie"
        categorie = db.find_all(sql)
        print("cate>>", callable)
        # 存入缓存
        r.str_add("categorie", json.dumps(categorie))
    return categorie

# 标签
def getTaglist():
    label = r.str_get("label")
    if label:
        label = json.loads(label)
    else:
        sql = "select * from label"
        label = db.find_all(sql)
        r.str_add("label", json.dumps(label))
    return label


@goods_blue.route("/getHomeGourse")
def getHomeGourse1():
    # 获取类型
    typelist = getTypelist()
    # 获取标签
    taglist = getTaglist()

    # 获取当前页面
    page = request.args.get("page")
    # 获取查询条件
    typeid = request.args.get("type")
    tagid = request.args.get("tagid")
    online = request.args.get("online")
    # 排序条件
    orderbyid = request.args.get("orderbyid")

    where = ""
    if int(typeid) > 0:
        where = where + " and c.type=" + typeid

    if int(online) > 0:
        where = where + " and c.online=" + online

    if int(tagid) > 0:
        tsql = "select count(c.id) as tcount from courses as c inner join courses_label as cl on cl.coursesid=c.id  where cl.labelid=" + tagid
        sql = "select * from courses as c inner join courses_label as cl on cl.coursesid=c.id  where cl.labelid=" + tagid
    else:
        sql = "select * from courses as c  where 1=1"
        tsql = "select count(c.id) as  tcount   from courses as c  where 1=1"

    if where:
        sql = sql + " " + where
        tsql = tsql + " " + where

    if int(orderbyid) > 0:
        if int(orderbyid) == 1:
            sql = sql + " order by c.id desc"
        else:
            sql = sql + " order by c.click_num desc"

    # 查询总共有多少条
    res = db.findone(tsql)
    total = res['tcount']
    page_size = 2
    start = (int(page) - 1) * page_size
    sql = sql + " limit %d,%d" % (start, page_size)
    courselist = db.find_all(sql)

    return jsonify({"code": 200, "tlist": typelist, 'taglist': taglist, 'clist': courselist, "total": total,
                    "page_size": page_size})


# 获取用户
@goods_blue.route("/getuser")
def getuser():
    # id = json.loads(request.data)
    sql = "select * from users where id=1"
    res = db.findone(sql)
    print(res)
    data = [{"id":res["id"],"name":res["name"],"email":res["email"],"mobile":res["mobile"],"password":res["password"],"type":res["type"]}]
    return jsonify({"code": 200,"msg":"获取成功", "data":data})

# 获取vip
@goods_blue.route("/getviplist")
def getviplist():
    sql ="select * from vip_setting"
    res = db.find_all(sql)
    list =[{"id":i['id'],"name":i["name"],"type":i['vtype'],"price":float(i['price'])} for i in res]
    return jsonify({"code":200,"list":list})


#用户升级vip
@goods_blue.route("/setVip",methods=['POST'])
def setVip():
    data = json.loads(request.data)
    userid = data['userid']
    types = data['type']
    #根据类型获取价格，根据类型计算到期时间
    sql ="select * from vip_setting where  vtype=%d"%(int(types))
    res = db.findone(sql)
    #写入vip订单表
    price = float(res['price'])
    #获取当前时间转成字符串
    stime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
    days1 = 365
    if int(types) == 1:
        days1=30
    #计算一年后或者一个月后日期
    etime = datetime.datetime.now()+datetime.timedelta(days=days1)
    etime = datetime.datetime.strftime(etime,"%Y-%m-%d %H:%M:%S")
    sql1 = "insert into vip_order(userid,type,price,create_time,pay_status,expires_time) values(%d,%d,%f,'%s',1,'%s')"%(int(userid),int(types),price,stime,etime)
    id = db.add(sql1)
    db.commit()
    #支付
    alipay = get_ali_object()
    # 生成支付的url
    query_params = alipay.direct_pay(
    subject=str(userid)+"购买vip服务",  # 商品简单描述
    out_trade_no=id,  # 用户购买的商品订单号（每次不一样） 20180301073422891
    total_amount=price,  # 交易金额(单位: 元 保留俩位小数)
    )
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
    return jsonify({"code":200,'url':pay_url})


from utils.comm import get_ali_object
import time
# @goods_blue.route("/testalipay")
# def testalipay():
#     alipay = get_ali_object()
#     # 生成支付的url
#     query_params = alipay.direct_pay(
#     subject="test",  # 商品简单描述
#     out_trade_no="asfasf"+str(time.time()),  # 用户购买的商品订单号（每次不一样） 20180301073422891
#     total_amount=100,  # 交易金额(单位: 元 保留俩位小数)
#     )
#     pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
#     return redirect(pay_url)


# #跳转到支付页面
# @goods_blue.route("/callback")
# def callback():
#     trade_no = request.args.get('trade_no') #流水号
#     out_trade_no = request.args.get('out_trade_no') #订单号
#     #加上事务处理
#     #更新订单状态
#     try:
#         stime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
#         sql= "update vip_order set pay_status=2,pay_time='%s',transaction_no='%s' where id=%d"%(stime,trade_no,int(out_trade_no))
#         db.add(sql)
#         #更新用户为vip状态
#         #查询订单对应的用户
#         sql1 = "select userid from vip_order where id=%d"%(int(out_trade_no))
#         user = db.findone(sql1)
#         usql = "update users set type=2 where id=%d"%(int(user['userid']))
#         db.add(usql)
#         db.commit()
#     except:
#         db.rollback()
#     #跳转到个人中心页面，我的订单页面
#     return redirect("http://localhost:8080/#/")


def updateorders(out_trade_no,trade_no):
    #加上事务处理
    #更新订单状态
    try:
        stime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
        sql= "update vip_order set pay_status=2,pay_time='%s',transaction_no='%s' where id=%d"%(stime,trade_no,int(out_trade_no))
        db.add(sql)
        #更新用户为vip状态
        #查询订单对应的用户
        sql1 = "select userid from vip_order where id=%d"%(int(out_trade_no))
        user = db.findone(sql1)
        usql = "update users set type=2 where id=%d"%(int(user['userid']))
        db.add(usql)
        #从队列中删除
        r.sortedset_delete("orderpayfail",out_trade_no)
        db.commit()
    except:
        db.rollback()


import requests
#对支付成功回调失败的订单处理
@goods_blue.route("/checkalipayorder")
def checkalipayorder():
    # "orderpayfail"
    #从队列中获取订单号 一分钟前
    endtime = int(time.time())
    list = r.sortedset_getbyscore("orderpayfail",0,endtime)
    #根据订单号去支付宝查询支付结果
    alipay = get_ali_object()
    for i in list:
        orderno = i.decode()
        # 生成支付的url
        query_params = alipay.query_pay(
        out_trade_no=orderno,  # 用户购买的商品订单号（每次不一样） 20180301073422891
        )
        pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）

        res = requests.get(pay_url)
        mes = json.loads(res.content)
        if mes['alipay_trade_query_response']['code'] == "10000":
            #根据结果更新订单状态
            updateorders(orderno,mes['alipay_trade_query_response']['trade_no'])
        else:
            #把订单状态改为支付失败
            paytime = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S")
            sql= "update vip_order set pay_status=3,pay_time='%s' where id=%d"%(int(orderno),paytime)
            db.add(sql)
            db.commit()
            #从队列中移除
            r.sortedset_delete("orderpayfail",orderno)


#课程详情页面
@goods_blue.route("/detail")
def detail():
    #获取课程id
    id = request.args.get('id')
    #从缓存中查询课程及章节信息
    key = "course"+str(id)
    skey = "section"+str(id)
    # r.del_key(key)
    # r.del_key(skey)
    course = r.str_get(key)
    if course:
        print("缓存")
        section = r.str_get(skey)
        return jsonify({"code":200,'clist':json.loads(course),'slist':json.loads(section)})
    #如果存在直接返回，如果不存在查询数据库
    else:
        print("查询")
        sql = "select c.id,s.id as sid,c.name,c.description,c.collent_sum,s.title,s.url,c.type from courses as c inner join sections as s on c.id=s.coursesid where c.id=%d order by s.sort asc"%(int(id))
        print(sql)
        res = db.find_all(sql)
        section=[]
        #获取课程下所有的章节
        for i in res:
            section.append({"id":i['sid'],"title":i['title'],"url":i['url'],"courseid":i['id']})
        #获取课程信息
        course = [{"id":res[0]['id'],"name":res[0]['name'],'description':res[0]['description'],'collent_sum':res[0]['collent_sum'],"type":res[0]['type']}]
        #数据解析存入redis
        r.str_add(key,json.dumps(course))
        r.str_add(skey,json.dumps(section))
        return jsonify({"code":200,'clist':course,'slist':section})


@goods_blue.route("/getVideo")
def getVideo():
    # 获取用户id和章节id
    courseid = request.args.get("courseid")
    userid = request.args.get("userid")
    id = request.args.get("id")
    # 通过用户表查询用户级别
    sql = "select type from users where id=%d"%(int(userid))
    user = db.find_all(sql)
    # 通过课程id查询课程的类型
    key = "course"+str(courseid)
    course = r.str_get(key)
    if course:
        course=json.loads(course)
        ctype=course[0]['type']
        if int(ctype) == 2:
            # 会员可看
            if int(user[0]["type"]) != 2:
                return jsonify({"code": 10011, "msg":"此用户没有权限观看"})
        # 课程type=3  需要购买课程
        if int(ctype) == 3:
            bsql = "select * from user_courses where userid=%d and courseid=%d"%(int(userid),int(courseid))
            bres = db.findone(bsql)
            if not bres:
                return jsonify({"code": 10012, "msg":"此课程需要单独购买"})
        # 获取此课程下的所有章节
        skey = "section"+str(courseid)
        sections = json.loads(r.str_get(skey))
        for i in sections:
            if int(i["id"]) == int(id):
                url = i["url"]
        # 返回此章节播放的url
        return jsonify({"code": 200, "msg":"获取成功", "url":url})
    else:
        return jsonify({"code":10010})
    # 用户权限和课程权限的判断